SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE  PROCEDURE [dbo].[FSIC_SOI_vc]
AS --***re-insert these lines




    BEGIN

 
        DECLARE @FundID VARCHAR(10)
        SET @FundID = 'FSIC'
        SET FMTONLY ON;
        DECLARE @fmtonlyON BIT
        SET @fmtonlyON = 0
        IF ( 1 = 0 ) 
            BEGIN
                SET @fmtonlyON = 1
            END
        SET FMTONLY OFF;
           
           
        SET NOCOUNT ON;
 
        DECLARE @ExportId INT = ( SELECT    MAX(ExportId)
                                  FROM      FSFundModel_VALCOMM.Reporting.Export
                                  WHERE     fundid = @FundID
                                )
 
 
 
        DECLARE @Data TABLE
            (
              instrumentID INT ,
              PortfolioID VARCHAR(MAX) ,
              IssuerName VARCHAR(MAX) ,
              FootNotes VARCHAR(MAX) ,
              Industry VARCHAR(MAX) ,
              EBITDA DECIMAL(38, 10) ,
              NBSReason VARCHAR(MAX) ,
              PctProp DECIMAL(38, 10) ,
              PricingSource VARCHAR(MAX) ,
              InvestmentRating INT ,
              MoodysRating VARCHAR(MAX) ,
              BidPr DECIMAL(38, 10) ,
              AskPr DECIMAL(38, 10) ,
              TodaysPrice DECIMAL(38, 10) ,
              Rate VARCHAR(MAX) ,
              Maturity DATE ,
              PrincipalAmt DECIMAL(38, 10) ,
              AmortizedCost DECIMAL(38, 10) ,
              FairVal DECIMAL(38, 10) ,
              PIK DECIMAL(38, 10) ,
              RateType VARCHAR(MAX) ,
              BaseRateType VARCHAR(MAX) ,
              FixedRate DECIMAL(38, 10) ,
              Spread DECIMAL(38, 10) ,
              FLOOR DECIMAL(38, 10) ,
              InstrClass VARCHAR(MAX) ,
              SortOrder INT ,
              QualAsset BIT ,
              CostDiscCurrPrc DECIMAL(38, 10) ,
              instTypeDescr VARCHAR(MAX) ,
              FinType VARCHAR(MAX) ,
              IncomeAtCurrentPrice DECIMAL(38, 10) ,
              OriginalCost DECIMAL(38, 10) ,
              shares DECIMAL(38, 10) ,
              OriginalPurchaseDate DATE ,
              AtCost VARCHAR(MAX) ,
              Qualified VARCHAR(MAX)
            )
--FORMAT_ON
--FORMAT_OFF
        DECLARE @ConsolidatedData TABLE
            (
              instrumentID INT ,
              PortfolioID VARCHAR(MAX) ,
              IssuerName VARCHAR(MAX) ,
              FootNotes VARCHAR(MAX) ,
              Industry VARCHAR(MAX) ,
              EBITDA DECIMAL(38, 10) ,
              NBSReason VARCHAR(MAX) ,
              PctProp DECIMAL(38, 10) ,
              PricingSource VARCHAR(MAX) ,
              InvestmentRating INT ,
              MoodysRating VARCHAR(MAX) ,
              BidPr DECIMAL(38, 10) ,
              AskPr DECIMAL(38, 10) ,
              TodaysPrice DECIMAL(38, 10) ,
              Rate VARCHAR(MAX) ,
              Maturity DATE ,
              PrincipalAmt DECIMAL(38, 10) ,
              AmortizedCost DECIMAL(38, 10) ,
              FairVal DECIMAL(38, 10) ,
              PIK DECIMAL(38, 10) ,
              RateType VARCHAR(MAX) ,
              BaseRateType VARCHAR(MAX) ,
              FixedRate DECIMAL(38, 10) ,
              Spread DECIMAL(38, 10) ,
              FLOOR DECIMAL(38, 10) ,
              InstrClass VARCHAR(MAX) ,
              SortOrder INT ,
              QualAsset BIT ,
              CostDiscCurrPrc DECIMAL(38, 10) ,
              instTypeDescr VARCHAR(MAX) ,
              FinType VARCHAR(MAX) ,
              IncomeAtCurrentPrice DECIMAL(38, 10) ,
              OriginalCost DECIMAL(38, 10) ,
              shares DECIMAL(38, 10) ,
              OriginalPurchaseDate DATE ,
              atcost VARCHAR(MAX) ,
              Qualified VARCHAR(MAX)
            )
--FORMAT_ON
--FORMAT_OFF
        DECLARE @Results TABLE
            (
              InstrumentID INT ,
              IssuerName VARCHAR(MAX) ,
              FootNotes VARCHAR(MAX) ,
              Industry VARCHAR(MAX) ,
              EBITDA VARCHAR(MAX) ,
              NBSReason VARCHAR(MAX) ,
              PctProp DECIMAL(38, 10) ,
              PricingSource VARCHAR(MAX) ,
              InvestmentRating INT ,
              MoodysRating VARCHAR(MAX) ,
              BidPr DECIMAL(38, 10) ,
              AskPr DECIMAL(38, 10) ,
              TodaysPrice DECIMAL(38, 10) ,
              Rate VARCHAR(MAX) ,
              FLOOR VARCHAR(MAX) ,
              Maturity VARCHAR(MAX) ,
              PrincipalAmt DECIMAL(38, 10) ,
              AmortizedCost DECIMAL(38, 10) ,
              FairVal DECIMAL(38, 10) ,
              sortOrder INT ,
              InstrType VARCHAR(MAX) ,
              IncomeAtCurrentPrice DECIMAL(38, 10) ,
              OriginalCost DECIMAL(38, 10) ,
              shares DECIMAL(38, 10) ,
              OriginalPurchaseDate DATE ,
              atcost VARCHAR(MAX) ,
              Qualified VARCHAR(MAX)
            )
        INSERT  INTO @Data
                ( instrumentID ,
                  PortfolioID ,
                  IssuerName ,
                  FootNotes ,
                  Industry ,
                  EBITDA ,
                  NBSReason ,
                  PctProp ,
                  PricingSource ,
                  InvestmentRating ,
                  MoodysRating ,
                  BidPr ,
                  AskPr ,
                  TodaysPrice ,
                  Rate ,
                  Maturity ,
                  PrincipalAmt ,
                  AmortizedCost ,
                  FairVal ,
                  PIK ,
                  RateType ,
                  BaseRateType ,
                  FixedRate ,
                  Spread ,
                  FLOOR ,
                  InstrClass ,
                  SortOrder ,
                  QualAsset ,
                  CostDiscCurrPrc ,
                  instTypeDescr ,
                  FinType ,
                  IncomeAtCurrentPrice ,
                  OriginalCost ,
                  shares ,
                  OriginalPurchaseDate ,
                  AtCost ,
                  Qualified
				
                )
                SELECT  i.InstrumentId ,
                        a.PortfolioId ,
                        iss.IssuerName ,
                        NULL ,
                        Iss.SubSector ,
                        Iss.TTMEBITDA ,
                        ah.NBSReason ,
                        CASE WHEN ah.NBSReason <> '' THEN a.mktval
                             ELSE '0'
                        END ,
                        i.PricingVendor ,
                        i.AssQualRat ,
                        i.MoodysRating ,
                        pr.Bid ,
                        pr.Offer ,
                        a.TodayPrice ,
                        NULL ,
                        i.MaturityDate ,
                        a.EndAdjAmt ,
                        a.AmortCost ,
                        a.MktVal ,
                        i.PIK ,
                        i.RateType ,
                        i.BaseRateType ,
                        i.FixedRate ,
                        i.Spread ,
                        i.Floor ,
                        id.InstrClass ,
                        id.SortOrder ,
                        fi.IsQualified ,
                        a.PctCostDiscCurrPrc ,
                        id.InstTypeDescr ,
                        id.FinType ,
                        a.TotalIntCurrPurchPrc ,
                        a.Cost ,
                        a.Shares ,
                        a.PurchDate ,
                        i.PricingMethodType ,
                        NULL
                FROM    FSFundModel_VALCOMM.Reporting.Asset a
                        INNER JOIN FSFundModel_VALCOMM.Reporting.Instrument i ON i.ExportId = a.ExportId
                                                              AND a.InstrumentId = i.InstrumentId
                        INNER JOIN FSFundModel_VALCOMM.Reporting.Issuer iss ON iss.ExportId = a.ExportId
                                                              AND i.IssuerId = iss.IssuerId
                        INNER JOIN FSFundModel_VALCOMM.Reporting.InstrumentDefinition id ON i.InstDefId = id.InstDefId
                                                              AND id.ExportId = a.ExportId
                        INNER JOIN FSFundModel_VALCOMM.Reporting.FundIssuer fi ON a.ExportId = fi.ExportId
                                                              AND fi.IssuerId = iss.IssuerId
                        INNER JOIN FSFundModel_VALCOMM.Reporting.Portfolio p ON a.ExportId = p.ExportId
                                                              AND a.PortfolioId = p.PortfolioId
                        INNER JOIN FSFundModel_VALCOMM.Reporting.Fund f ON a.ExportId = f.ExportId
                        INNER JOIN reportingscripts.[Valcomm].AssetHelper(@exportid) AH ON ah.AssetIntrnlId = a.AssetIntrnlId
                        LEFT JOIN FSFundModel_VALCOMM.Reporting.Pricing Pr ON a.ExportId = pr.ExportId
                                                              AND CAST(f.CurrNavDate AS DATE) = pr.PriceDate
                                                              AND i.ExtId = pr.PricingId
                WHERE   a.ExportId = @ExportId
                        AND a.IsActiveEndOfDay = 1


        UPDATE  @Data
        SET     FootNotes = COALESCE(FootNotes + '(ASF)', '(ASF)')
        WHERE   LTRIM(RTRIM(PortfolioID)) = 'ARCH'
        UPDATE  @Data
        SET     FootNotes = COALESCE(FootNotes + '(BSF)', '(BSF)')
        WHERE   LTRIM(RTRIM(PortfolioID)) = 'BROAD'
        UPDATE  @Data
        SET     FootNotes = COALESCE(FootNotes + '(LSF)', '(LSF)')
        WHERE   PortfolioID = 'LOCUS'
        UPDATE  @Data
        SET     FootNotes = COALESCE(FootNotes + '(RSF)', '(RSF)')
        WHERE   LTRIM(RTRIM(PortfolioID)) = 'RACES'
        UPDATE  @Data
        SET     FootNotes = COALESCE(FootNotes + '(WSF)', '(WSF)')
        WHERE   LTRIM(RTRIM(PortfolioID)) = 'WALN'



        INSERT  INTO @ConsolidatedData
                ( instrumentID ,
                  PortfolioID ,
                  IssuerName ,
                  FootNotes ,
                  Industry ,
                  EBITDA ,
                  NBSReason ,
                  PctProp ,
                  PricingSource ,
                  InvestmentRating ,
                  MoodysRating ,
                  BidPr ,
                  AskPr ,
                  TodaysPrice ,
                  Rate ,
                  Maturity ,
                  PrincipalAmt ,
                  AmortizedCost ,
                  FairVal ,
                  PIK ,
                  RateType ,
                  BaseRateType ,
                  FixedRate ,
                  Spread ,
                  FLOOR ,
                  InstrClass ,
                  SortOrder ,
                  QualAsset ,
                  CostDiscCurrPrc ,
                  instTypeDescr ,
                  FinType ,
                  IncomeAtCurrentPrice ,
                  OriginalCost ,
                  shares ,
                  OriginalPurchaseDate ,
                  atcost ,
                  Qualified
                )
                SELECT  d.InstrumentId ,
                        MAX(d.PortfolioId) ,
                        MAX(d.IssuerName) ,
                        NULL ,
                        MAX(d.industry) ,
                        MAX(d.EBITDA) ,
                        MAX(d.NBSReason) ,
                        SUM(d.pctprop) ,
                        MAX(d.PricingSource) ,
                        MAX(d.investmentrating) ,
                        MAX(d.moodysrating) ,
                        MAX(d.bidpr) ,
                        MAX(d.askpr) ,
                        MAX(d.todaysprice) ,
                        NULL ,
                        MAX(d.Maturity) ,
                        SUM(d.PrincipalAmt) ,
                        SUM(d.AmortizedCost) ,
                        SUM(d.FairVal) ,
                        MAX(d.PIK) ,
                        MAX(d.RateType) ,
                        MAX(d.BaseRateType) ,
                        MAX(d.FixedRate) ,
                        MAX(d.Spread) ,
                        MAX(d.Floor) ,
                        MAX(d.InstrClass) ,
                        MAX(d.SortOrder) ,
                        d.QualAsset ,
                        MAX(d.CostDiscCurrPrc) ,
                        MAX(d.instTypeDescr) ,
                        MAX(d.FinType) ,
                        SUM(d.IncomeAtCurrentPrice) ,
                        SUM(d.originalcost) ,
                        SUM(d.shares) ,
                        MIN(d.OriginalPurchaseDate) ,
                        MAX(d.atcost) ,
                        NULL
                FROM    @Data d
                GROUP BY d.instrumentId ,
                        d.QualAsset

 
 
        INSERT  INTO @Results
                ( InstrumentID ,
                  IssuerName ,
                  Industry ,
                  EBITDA ,
                  NBSReason ,
                  PctProp ,
                  PricingSource ,
                  InvestmentRating ,
                  MoodysRating ,
                  BidPr ,
                  AskPr ,
                  TodaysPrice ,
                  FootNotes ,
                  PrincipalAmt ,
                  AmortizedCost ,
                  FairVal ,
                  sortOrder ,
                  InstrType ,
                  IncomeAtCurrentPrice ,
                  OriginalCost ,
                  shares ,
                  OriginalPurchaseDate ,
                  atcost
                )
                SELECT 
	DISTINCT            d.InstrumentID ,
                        d.IssuerName ,
                        d.Industry ,
                        d.EBITDA ,
                        d.NBSReason ,
                        CASE WHEN d.fairval = '0' THEN '0'
                             ELSE d.pctprop / d.FairVal
                        END ,
                        d.PricingSource ,
                        d.InvestmentRating ,
                        d.MoodysRating ,
                        d.Bidpr ,
                        d.askpr ,
                        d.TodaysPrice ,
                        STUFF(( SELECT  '' + tmp.FootNotes
                                FROM    ( SELECT 
			 DISTINCT                               d.instrumentID ,
                                                    d.IssuerName ,
                                                    d.FootNotes
                                          FROM      @Data d
                                        ) AS tmp
                                WHERE   tmp.instrumentid = d.instrumentID
                              FOR
                                XML PATH('')
                              ), 1, 0, '') ,
                        d.PrincipalAmt ,
                        d.AmortizedCost ,
                        d.FairVal ,
                        d.sortorder ,
                        d.instrClass ,
                        d.IncomeAtCurrentPrice ,
                        d.OriginalCost ,
                        d.shares ,
                        d.OriginalPurchaseDate ,
                        d.atcost
                FROM    @ConsolidatedData d
                ORDER BY d.sortOrder ASC ,
                        d.issuerName ASC ,
                        d.instrumentid 

        UPDATE  r
        SET     r.FootNotes = COALESCE(r.FootNotes + '(Non-Qualified)',
                                       '(Non-Qualified)')
        FROM    @Results r
                INNER JOIN @ConsolidatedData d ON r.IssuerName = d.issuerName
        WHERE   d.QualAsset = 0
  
        UPDATE  r
        SET     r.Qualified = COALESCE(r.Qualified + 'Non-Qualified',
                                       'Non-Qualified')
        FROM    @Results r
                INNER JOIN @ConsolidatedData d ON r.IssuerName = d.issuerName
        WHERE   d.QualAsset = 0
  
  
        UPDATE  r
        SET     r.Rate = CASE WHEN d.RateType LIKE '%Floating%'
                              THEN CASE WHEN d.BaseRateType LIKE '%LIBOR%'
                                        THEN 'L+'
                                             + CAST (CAST	(( d.spread
                                                              * 10000 ) AS DECIMAL(38,
                                                              0)) AS VARCHAR(MAX))
                                        WHEN d.BaseRateType LIKE '%EURIBOR%'
                                        THEN 'EURIBOR+'
                                             + CAST (CAST	(( d.spread
                                                              * 10000 ) AS DECIMAL(38,
                                                              0)) AS VARCHAR(MAX))
                                        WHEN d.BaseRateType LIKE '%PRIME%'
                                        THEN 'Prime+'
                                             + CAST (CAST	(( d.spread
                                                              * 10000 ) AS DECIMAL(38,
                                                              0)) AS VARCHAR(MAX))
                                   END
                         END
        FROM    @Results r
                INNER JOIN @ConsolidatedData d ON r.InstrumentID = d.instrumentId

        UPDATE  r
        SET     r.Rate = CASE WHEN d.rateType = 'Fixed'
                              THEN CAST (CAST	(( d.fixedRate * 100 ) AS DECIMAL(38,
                                                              1)) AS VARCHAR(MAX))
                                   + '%'
                              ELSE r.Rate
                         END
        FROM    @Results r
                INNER JOIN @ConsolidatedData d ON r.InstrumentID = d.instrumentId



        UPDATE  r --%O - % matches for Collateralized Secs 
        SET     r.Rate = CASE WHEN d.rateType = 'None'
                              THEN CASE WHEN d.instTypeDescr LIKE '%O - %'
                                        THEN CAST (CAST	(( d.CostDiscCurrPrc
                                                           * 100 ) AS DECIMAL(38,
                                                              1)) AS VARCHAR(MAX))
                                             + '%'
                                   END
                              ELSE r.Rate
                         END
        FROM    @Results r
                INNER JOIN @ConsolidatedData d ON r.InstrumentID = d.instrumentID

        UPDATE  r
        SET     r.FLOOR = CASE WHEN d.RateType LIKE '%Floor%'
                               THEN CASE WHEN d.BaseRateType LIKE '%LIBOR%'
                                         THEN CAST (CAST (( d.FLOOR * 100 ) AS DECIMAL(38,
                                                              1)) AS VARCHAR(MAX))
                                              + '% LIBOR Floor'
                                         WHEN d.baseRateType LIKE '%EURIBOR%'
                                         THEN CAST (CAST (( d.FLOOR * 100 ) AS DECIMAL(38,
                                                              1)) AS VARCHAR(MAX))
                                              + '% EURIBOR Floor'
                                         WHEN d.baseRateType LIKE '%PRIME%'
                                         THEN CAST (CAST (( d.FLOOR * 100 ) AS DECIMAL(38,
                                                              1)) AS VARCHAR(MAX))
                                              + '% Prime Floor'
                                    END
                          END
        FROM    @Results r
                INNER JOIN @ConsolidatedData d ON r.instrumentid = d.instrumentid


        UPDATE  r
        SET     r.maturity = CAST(( CAST (DATEPART(month, d.Maturity) AS VARCHAR(MAX))
                                    + '/'
                                    + CAST(DATEPART(DAY, d.Maturity) AS VARCHAR(MAX))
                                    + '/'
                                    + RIGHT(CAST(DATEPART(year, d.Maturity) AS VARCHAR(MAX)),
                                            2) ) AS VARCHAR(MAX))
        FROM    @Results r
                INNER JOIN @ConsolidatedData d ON R.InstrumentID = d.instrumentID

        SELECT  r.*
        FROM    @Results r
        WHERE   r.instrumentid NOT IN (
                SELECT  fci.instrumentID
                FROM    FSFundModel_VALCOMM.Reporting.FundCreditInstrument fci
                WHERE   fci.ExportId = ( SELECT MAX(e.ExportId)
                                         FROM   fsfundmodel_VALCOMM.Reporting.Export e
                                         WHERE  e.FundId = 'FSIC'
                                       )
                        AND fci.Drawn = '0' )
    END




GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Dbo/QuarterlyReporting/SEC', 'SCHEMA', N'dbo', 'PROCEDURE', N'FSIC_SOI_vc', NULL, NULL
GO
